package model.requirement7;

import model.DatabaseConnection;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StatisticsDAO {

    // 按日期统计销售额
    public List<String[]> getSalesByDate() throws Exception {
        List<String[]> results = new ArrayList<>();
        try (Connection connection = DatabaseConnection.getConnection()) {
            String query = "SELECT DATE(o.OrderTime) AS OrderDate, SUM(o.TotalPrice) AS TotalSales " +
                    "FROM Orders o " +
                    "WHERE o.OrderStatus = 'Completed' or o.OrderStatus = 'Pending' " + // 仅统计已完成和待处理的订单
                    "GROUP BY DATE(o.OrderTime) " +
                    "ORDER BY OrderDate;";
            PreparedStatement statement = connection.prepareStatement(query);
            ResultSet resultSet = statement.executeQuery();

            while (resultSet.next()) {
                results.add(new String[]{
                        resultSet.getString("OrderDate"),
                        String.format("%.2f", resultSet.getDouble("TotalSales"))
                });
            }
        }
        return results;
    }

    // 按品类统计销售额
    public List<String[]> getSalesByCategory() throws Exception {
        List<String[]> results = new ArrayList<>();
        try (Connection connection = DatabaseConnection.getConnection()) {
            String query = "SELECT c.ItemType, SUM(d.Quantity * c.Price) AS TotalSales " +
                    "FROM OrderDetails d " +
                    "JOIN menuitems c ON d.ItemID = c.ItemID " +
                    "JOIN Orders o ON d.OrderID = o.OrderID " +
                    "WHERE o.OrderStatus = 'Completed' " + // 仅统计已完成的订单
                    "GROUP BY c.ItemType " +
                    "ORDER BY TotalSales DESC;";
            PreparedStatement statement = connection.prepareStatement(query);
            ResultSet resultSet = statement.executeQuery();

            while (resultSet.next()) {
                results.add(new String[]{
                        resultSet.getString("ItemType"),
                        String.format("%.2f", resultSet.getDouble("TotalSales"))
                });
            }
        }
        return results;
    }

    // 按单品统计销售量和销售额
    public List<String[]> getSalesByItem() throws Exception {
        List<String[]> results = new ArrayList<>();
        try (Connection connection = DatabaseConnection.getConnection()) {
            String query = "SELECT c.ItemName, SUM(d.Quantity) AS TotalQuantity, SUM(d.Quantity * c.Price) AS TotalSales " +
                    "FROM OrderDetails d " +
                    "JOIN menuitems c ON d.ItemID = c.ItemID " +
                    "JOIN Orders o ON d.OrderID = o.OrderID " +
                    "WHERE o.OrderStatus = 'Completed' " + // 仅统计已完成的订单
                    "GROUP BY c.ItemName " +
                    "ORDER BY TotalQuantity DESC;";
            PreparedStatement statement = connection.prepareStatement(query);
            ResultSet resultSet = statement.executeQuery();

            while (resultSet.next()) {
                results.add(new String[]{
                        resultSet.getString("ItemName"),
                        String.valueOf(resultSet.getInt("TotalQuantity")),
                        String.format("%.2f", resultSet.getDouble("TotalSales"))
                });
            }
        }
        return results;
    }
}
